########################################################################
#  xls2csv2 - convert excel to csv and handle encoding
#  Copyright (C) 2007,8 Ehud Ben-Reuven
#  udi@benreuven.com
#
# This program is free software; you can redistribute it and/or
# modify it under the terms of the GNU General Public License
# as published by the Free Software Foundation version 2.
#
# This program is distributed in the hope that it will be useful,
# but WITHOUT ANY WARRANTY; without even the implied warranty of
# MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
# GNU General Public License for more details.
#
# You should have received a copy of the GNU General Public License
# along with this program; if not, write to the Free Software
# Foundation, Inc., 51 Franklin Street, Fifth Floor, Boston, MA  02110-1301, USA.
########################################################################
"""xls2csv2 [-e encoding] Excel-file-name CSV-file-name

write the Excel file in CSV format
handle unicode strings using specified encoding.
Example: xls2csv2 -e hebrew foo.xls foo.csv
"""
import sys, os, string
from optparse import OptionParser
try:
    import win32com.client
    import pythoncom
except ImportError:
        pass
import csv

def xls2csv2(xlfile,csvfile,encoding):
    try:
        xl = win32com.client.Dispatch ("Excel.Application")
 #      xl.Visible = 1
        wb = xl.Workbooks.Open (xlfile)
        xls = wb.ActiveSheet
        nr = xls.UsedRange.Rows.Count
        nc = xls.UsedRange.Columns.Count
        writer = csv.writer(open(csvfile, "wb"))
        for r in range(1,nr+1):
            row=[]
            for c in range(1,nc+1):
                cell = xls.Cells(r,c).Value
                if (not encoding is None) and isinstance(cell,unicode):
                    cell=cell.encode(encoding)
                row.append(cell)
            writer.writerow(row)
        wb.Close(SaveChanges=0)
        xl.Quit()
    except pythoncom.com_error, (hr, msg, exc, arg):
        print "Failed to convert excel file to CSV"
        print "The Excel call failed with code %d: %s" % (hr, msg)
        if exc is None:
            print "There is no extended error information"
        else:
            wcode, source, text, helpFile, helpId, scode = exc
            print "The source of the error is", source
            print "The error message is", text
            print "More info can be found in %s (id=%d)" % (helpFile, helpId)
        raise
    except:
        print "failed to write to csv file", sys.exc_info()[0]
        raise


def main():
    parser = OptionParser(__doc__)
    parser.add_option("-e", "--encoding", dest="encoding",
        help="Cell containing unicode will be encoded with this encoder, e.g., -e hebrew")
    (options, args) = parser.parse_args()
    if len(args) != 2:
        parser.error("Missing file name")
    xlfile = args[0]
    csvfile = args[1]
        
    if not os.path.exists(xlfile):
        print "\nFile %s not found." % (xlfile)
        sys.exit(1)

    try:
        xls2csv2(xlfile,csvfile,options.encoding)
    except:
        sys.exit(1)

if __name__ == "__main__":
    main()
